package com.student.dao.impl;

import com.student.dao.BaseDao;
import com.student.dao.StudentDao;
import com.student.pojo.College;
import com.student.pojo.Major;
import com.student.pojo.Student;

import java.util.List;

/**
 * @author lin945
 * @date 2020/4/25 13:14
 */
public class StudentDaoImpl extends BaseDao implements StudentDao{

    @Override
    public boolean existStudent(Student student) {

        String sql = "SELECT * FROM student WHERE id = ?";

        return querForOne(Student.class,sql,student.getId())!=null;
    }

    @Override
    public List<Student> queryStudentByName(String name) {

        String sql = "SELECT * FROM student WHERE NAME = ?";

        return querForList(Student.class,sql, name);
    }

    @Override
    public List<Student> queryStudentByName(int startIndex, int pageSize, String name) {
        String sql = "SELECT student.id,student.name,(CASE WHEN sex = '0' THEN '女' ELSE '男' END ) AS sex,birth,schoolday,major.name as major_id,college.name as college_id FROM student,major,college where student.major_id=major.id and student.college_id=college.id and student.name=? LIMIT ?,? ";

        return querForList(Student.class, sql,name, startIndex, pageSize);

    }

    @Override
    public boolean saveStudent(Student student) {

        String Id = student.getId();
        String Name = student.getName();
        String Sex = student.getSex();
        String Birth = student.getBirth();
        String Schoolday = student.getSchoolday();
        String Major_id = student.getMajor_id();
        String College_id = student.getCollege_id();

        String sql = "INSERT INTO student VALUES (?,?,?,?,?,?,?)";

        return update(sql,Id,Name,Sex,Birth,Schoolday,Major_id,College_id)!=-1;
    }

    @Override
    public boolean updateStudent(Student student) {

        String Id = student.getId();
        String Name = student.getName();
        String Sex = student.getSex();
        String Birth = student.getBirth();
        String Schoolday = student.getSchoolday();
        String Major_id = student.getMajor_id();
        String College_id = student.getCollege_id();

        String sql = "UPDATE student SET name=?,sex=?,birth=?,schoolday=?,major_id=?,college_id = ? WHERE id = ?";
        int update = update(sql, Name, Sex, Birth, Schoolday, Major_id, College_id, Id);
        return update!=0&&update!=-1;
    }

    @Override
    public boolean deleteStudent(Student student) {

        String sql = "DELETE FROM student WHERE id = ?";

        return update(sql,student.getId())!=0;
    }

    @Override
    public Student queryStudentById(String id) {
        String sql = "SELECT * FROM student WHERE id = ?";

        return querForOne(Student.class,sql, id);
    }

    @Override
    public List<Student> querStudentList() {

        String sql = "selecct * from student";

        List<Student> students = querForList(Student.class, sql);

        return students;
    }

    @Override
    public List<Student> querStudentlimtList(int startIndex, int pageSize) {

        String sql = "SELECT student.id,student.name,(CASE WHEN sex = '0' THEN '女' ELSE '男' END ) AS sex,birth,schoolday,major.name as major_id,college.name as college_id FROM student,major,college where student.major_id=major.id and student.college_id=college.id LIMIT ?,? ";

        List<Student> students = querForList(Student.class, sql, startIndex, pageSize);

        return students;
    }

    @Override
    public long getStudentCount() {

        String sql = "SELECT COUNT(id) FROM student";

        return userCount(sql);
    }

    public List<College> queryAllCollege() {

        String sql = "SELECT * FROM college";
        return querForList(College.class, sql);
    }

    public List<Major> queryMajorByCollege(Integer id){
        String sql="SELECT major.id,major.`name` as name,college.`name` as collegeName FROM major,college WHERE major.college_id=college.id AND major.`college_id`=?";
      return querForList(Major.class, sql, id);
    }

}